"""
Case Type   : JDBC+mysql和opengauss兼容B库对比
Case Name   : JDBC默认参数+TINYINT(n)不带zerofill属性+主键索引
Create At   : 2023/12/21
Owner       : ningyali
Description :
    1.读取通用tinyint SQL脚本
    2.复制jdbc到当前目录
    3.生成java文件,通过JDBC连接数据库,执行sql
    4.编译上一步生成的java文件，生成可执行文件
    5.执行上一步生成的可执行文件，对mysql和opengauss兼容B库结果进行对比
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功
    5.执行成功，结果一致
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.CONNECTORS.JDBC.CompatibilityB.DATATYPE. \
    Opengauss_Function_Connect_JDBC_Common import ComJDBC
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class MysqlTest15(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.mysql_node = Node('mysql5')
        self.comjdbc = ComJDBC()
        self.Constant = Constant()
        self.og_node = Node('PrimaryDbBmode')
        self.mysql_info = {'f_name': 'mysql_jdbc_datatype15',
                           'db_type': 'mysql',
                           'db_host': self.mysql_node.db_host,
                           'db_port': self.mysql_node.db_port,
                           'db_name': self.mysql_node.db_name,
                           'db_user': self.mysql_node.db_user,
                           'db_passwd': self.mysql_node.db_password,
                           'db_driver': 'com.mysql.jdbc.Driver',
                           'sql': ''}
        self.og_info = {'f_name': 'opengauss_jdbc_datatype15',
                        'db_type': 'opengauss',
                        'db_host': self.og_node.db_host,
                        'db_port': self.og_node.db_port,
                        'db_name': self.og_node.db_name,
                        'db_user': self.og_node.db_user,
                        'db_passwd': self.og_node.db_password,
                        'db_driver': 'org.opengauss.Driver',
                        'sql': ''}
        self.sql_path = os.path.join(
            'testcase', 'script', 'CompatibilityB', 'DATATYPE', 'TINYINT',
            'CompatibilityB_DataType_Tinyint_Case0015.sql')
        self.expect = "*** mysql\n" \
                      "--- openGauss\n" \
                      "***************\n" \
                      "*** 1,4 ****\n" \
                      "--- 1,12 ----\n" \
                      "Connection succeed\n" \
                      "--@owner: ningyali\n" \
                      "--@date: 2023/12/21\n" \
                      "--@testpoint: tinyint类型不带zerofill属性&主键索引\n" \
                      "***************\n" \
                      "*** 73,79 ****\n" \
                      "--step5:执行计划使用索引; expect:成功\n" \
                      "explain select * from t_tinyint0015 " \
                      "where c1 >50 and c1 < 100\n" \
                      "----查询结果----\n" \
                      "! 1|SIMPLE|t_tinyint0015|null|range|PRIMARY|PRIMARY|" \
                      "1|null|49|100.0|Using where\n" \
                      "--step6:删除部分数据; expect:成功\n" \
                      "delete from t_tinyint0015 where c1 < 50\n" \
                      "SQL SUCCESS\n" \
                      "--- 81,88 ----\n" \
                      "--step5:执行计划使用索引; expect:成功\n" \
                      "explain select * from t_tinyint0015 " \
                      "where c1 >50 and c1 < 100\n" \
                      "----查询结果----\n" \
                      "! Index Scan using t_tinyint0015_pkey on " \
                      "t_tinyint0015  (cost=0.00..24.37 rows=6 width=36)\n" \
                      "!   Index Cond: ((c1 > 50) AND (c1 < 100))\n" \
                      "--step6:删除部分数据; expect:成功\n" \
                      "delete from t_tinyint0015 where c1 < 50\n" \
                      "SQL SUCCESS\n" \
                      "***************\n" \
                      "*** 83,89 ****\n" \
                      "--step9:执行计划未使用索引; expect:成功\n" \
                      "explain select * from t_tinyint0015 " \
                      "where c1 >50 and c1 < 100\n" \
                      "----查询结果----\n" \
                      "! 1|SIMPLE|t_tinyint0015|null|ALL|null|null|null|" \
                      "null|1|100.0|Using where\n" \
                      "--step10:再次插入部分数据; expect:成功\n" \
                      "call insertdata(49)\n" \
                      "SQL SUCCESS\n" \
                      "--- 92,99 ----\n" \
                      "--step9:执行计划未使用索引; expect:成功\n" \
                      "explain select * from t_tinyint0015 " \
                      "where c1 >50 and c1 < 100\n" \
                      "----查询结果----\n" \
                      "! Seq Scan on t_tinyint0015  (cost=10000000000." \
                      "00..1000000002857.00 rows=6 width=36)\n" \
                      "!   Filter: ((c1 > 50) AND (c1 < 100))\n" \
                      "--step10:再次插入部分数据; expect:成功\n" \
                      "call insertdata(49)\n" \
                      "SQL SUCCESS\n" \
                      "***************\n" \
                      "*** 151,157 ****\n" \
                      "--step14:执行计划使用索引; expect:成功\n" \
                      "explain select * from t_tinyint0015 " \
                      "where c1 >50 and c1 < 100\n" \
                      "----查询结果----\n" \
                      "! 1|SIMPLE|t_tinyint0015|null|range|PRIMARY|PRIMARY|" \
                      "1|null|49|100.0|Using where\n" \
                      "--step15:再次查询; expect:成功\n" \
                      "select * from t_tinyint0015 where c1 >50 and " \
                      "c1 < 100 order by 1, 2, 3, 4\n" \
                      "----查询结果----\n" \
                      "--- 161,168 ----\n" \
                      "--step14:执行计划使用索引; expect:成功\n" \
                      "explain select * from t_tinyint0015 " \
                      "where c1 >50 and c1 < 100\n" \
                      "----查询结果----\n" \
                      "! Index Scan using t_tinyint0015_pkey on " \
                      "t_tinyint0015  (cost=0.00..8.27 rows=1 width=36)\n" \
                      "!   Index Cond: ((c1 > 50) AND (c1 < 100))\n" \
                      "--step15:再次查询; expect:成功\n" \
                      "select * from t_tinyint0015 where c1 >50 " \
                      "and c1 < 100 order by 1, 2, 3, 4\n" \
                      "----查询结果----\n" \
                      "***************\n" \
                      "*** 208,212 ****\n" \
                      "--- 219,231 ----\n" \
                      "----查询结果----\n" \
                      "49\n" \
                      "--step16:清理环境;expect:成功\n" \
                      "drop table if exists t_tinyint0015 cascade\n" \
                      "SQL SUCCESS"

    def test_1(self):
        text = 'step1:读取tinyint SQL脚本; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.log.info(self.sql_path)
        sql = self.comjdbc.read_sql_file(self.sql_path)
        self.assertNotEqual(len(sql.strip()), 0, f'执行失败：{text}')
        og_params = [['add', 1, 'set enable_opfusion=off;'],
                     ['add', 2, 'set enable_beta_opfusion=off;'],
                     ['add', 3, 'set enable_bitmapscan=off;'],
                     ['add', 4, 'set enable_seqscan=off;'],
                     ['add', -1, 'set enable_opfusion=on;'],
                     ['add', -2, 'set enable_beta_opfusion=on;'],
                     ['add', -3, 'set enable_bitmapscan=on;'],
                     ['add', -4, 'set enable_seqscan=on;']]
        og_sql = self.comjdbc.characteristic_sql(sql, *og_params)
        self.mysql_info['sql'] = sql
        self.og_info['sql'] = og_sql

        text = 'step2:复制jdbc到当前目录; expect:执行成功'
        self.log.info(f'-----{text}-----')
        res = self.comjdbc.get_jdbc(self.mysql_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'mysql执行失败：{text}')
        res = self.comjdbc.get_jdbc(self.og_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'opengauss执行失败：{text}')

        text = 'step3:生成java文件,通过JDBC连接数据库,执行sql; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.comjdbc.write_java_file(**self.mysql_info)
        self.comjdbc.write_java_file(**self.og_info)

        text = 'step4:编译上一步生成的java文件，生成可执行文件; expect:执行成功'
        self.log.info(f'-----{text}-----')
        res = self.comjdbc.compile_java_file(self.mysql_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'mysql执行失败：{text}')
        res = self.comjdbc.compile_java_file(self.og_info['db_type'])
        self.assertEqual(len(res.strip()), 0, f'opengauss执行失败：{text}')

        text = 'step5:执行上一步生成的可执行文件，对mysql和opengauss兼容B库结果进行对比; ' \
               'expect:执行成功，结果一致'
        self.log.info(f'-----{text}-----')
        mysql_res, _, mysql_time = self.comjdbc.run_java_file(
            self.mysql_info['db_type'])
        og_res, _, og_time = self.comjdbc.run_java_file(
            self.og_info['db_type'])

        self.log.info('-----对比mysql和opengauss兼容B库执行时长-----')
        t_diff, des = self.comjdbc.compare_run_time(mysql_time, og_time,
                                                    'mysql', 'openGauss')
        self.assertTrue(
            'openGauss性能更优' in des or 'mysql与openGauss性能一致' in des)

        self.log.info('-----对比mysql和opengauss兼容B库执行结果-----')
        diff = self.comjdbc.compare_sql_res(mysql_res, og_res, 'mysql',
                                            'openGauss')

        self.log.info('-----对比预期结果与实际运行结果-----')
        ignore = r'1\|SIMPLE\|t_tinyint0013\|null\|ALL\|null\|null\|null' \
                 r'\|null\|.*?\|.*?\|Using where'
        diff_diff = self.comjdbc.compare_sql_err_res(self.expect, diff,
                                                     '预期结果', '实际结果',
                                                     ignore)
        self.assertEqual(len(diff_diff.strip()), 0, '预期结果与实际运行结果不一致')

    def tearDown(self):
        self.log.info('----run teardown----')
        self.log.info('-----清理环境-----')
        self.comjdbc.remove_java_file()
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
